package cn.com.headfree.sysmenu.dao.impl;

import cn.com.headfree.entity.IscUser;
import cn.com.headfree.sysmenu.dao.IRoleDao;
import cn.com.headfree.sysmenu.entity.TSysRole;
import cn.com.headfree.sysmenu.vo.SysRoleVo;
import com.gta.edu.sdk.base.dao.impl.BaseDaoImpl;
import com.gta.edu.sdk.common.dto.Page;
import com.gta.edu.sdk.util.StringUtil;
import org.apache.log4j.Logger;
import org.springframework.stereotype.Repository;

import java.sql.Timestamp;
import java.util.*;

@Repository("roleDao")
public class RoleDaoImpl extends BaseDaoImpl<TSysRole> implements IRoleDao {
    private static final Logger LOG = Logger.getLogger(RoleDaoImpl.class);

    //查询角色全部列表
    @Override
    public List<TSysRole> findRoleList(SysRoleVo sysRole, String orgId) {
        String sql = "  select distinct t.id,t.org_id,tod.name as org_name,t.role_name,t.role_code,t.role_type,t.status,t.app_id,t.domain_id,t.description,t.create_time,t.role_sort " +
                " from t_sys_role t left join t_org_dept tod on t.org_id=tod.id " +
                " where 1=1 " ;
        StringBuilder sqlBuilder = new StringBuilder(sql);

        ArrayList<Object> arrayList = new ArrayList<Object>();
        //当前登录用户的orgId
        if (!StringUtil.isNullOrEmpty(orgId)) {
            sqlBuilder.append(" and t.org_id in ( select tod.id from t_org_dept tod where tod.path like concat('%',?,'%')) ");
            arrayList.add(orgId);
        }
        //角色名称
        if (!StringUtil.isNullOrEmpty(sysRole.getRoleName())) {
            sqlBuilder.append(" AND t.role_name like concat('%',?,'%') ");
            arrayList.add(sysRole.getRoleName());
        }
        //状态
        if (!StringUtil.isNullOrEmpty(sysRole.getStatus())) {
            sqlBuilder.append(" AND t.status = ? ");
            arrayList.add(sysRole.getStatus());
        }
        //创建时间开始
        if (null!=sysRole.getBeginTime()) {
            sqlBuilder.append(" and date_format(t.create_time,'%y%m%d') >= date_format(?,'%y%m%d') ");
            arrayList.add(sysRole.getBeginTime());
        }
        //创建时间结束
        if (null!=sysRole.getEndTime()) {
            sqlBuilder.append(" and date_format(t.create_time,'%y%m%d') <= date_format(?,'%y%m%d') ");
            arrayList.add(sysRole.getEndTime());
        }

        sqlBuilder.append(" order by t.org_id,t.role_sort,t.create_time  ");
        return this.findAllBySQL(TSysRole.class, sqlBuilder.toString(), null,false, arrayList.toArray());
    }

    //查询角色分页列表
    @Override
    public void getPageRole(Page<TSysRole> page, SysRoleVo sysRole, String orgId) {
        String sql = "  select distinct t.id,t.org_id,tod.name as org_name,t.role_name,t.role_code,t.role_type,t.status,t.app_id,t.domain_id,t.description,t.create_time,t.role_sort " +
                " from t_sys_role t left join t_org_dept tod on t.org_id=tod.id " +
                " where 1=1 " ;
        StringBuilder sqlBuilder = new StringBuilder(sql);

        ArrayList<Object> arrayList = new ArrayList<Object>();
        //当前登录用户的orgId
        if (!StringUtil.isNullOrEmpty(orgId)) {
            sqlBuilder.append(" and t.org_id in ( select tod.id from t_org_dept tod where tod.path like concat('%',?,'%')) ");
            arrayList.add(orgId);
        }
        //角色名称
        if (!StringUtil.isNullOrEmpty(sysRole.getRoleName())) {
            sqlBuilder.append(" AND t.role_name like concat('%',?,'%') ");
            arrayList.add(sysRole.getRoleName());
        }
        //角色Code
        if (!StringUtil.isNullOrEmpty(sysRole.getRoleCode())) {
            sqlBuilder.append(" AND t.role_code like concat('%',?,'%') ");
            arrayList.add(sysRole.getRoleCode());
        }
        //状态
        if (!StringUtil.isNullOrEmpty(sysRole.getStatus())) {
            sqlBuilder.append(" AND t.status = ? ");
            arrayList.add(sysRole.getStatus());
        }
        //创建时间开始
        if (null!=sysRole.getBeginTime()) {
            sqlBuilder.append(" and date_format(t.create_time,'%y%m%d') >= date_format(?,'%y%m%d') ");
            arrayList.add(sysRole.getBeginTime());
        }
        //创建时间结束
        if (null!=sysRole.getEndTime()) {
            sqlBuilder.append(" and date_format(t.create_time,'%y%m%d') <= date_format(?,'%y%m%d') ");
            arrayList.add(sysRole.getEndTime());
        }

        sqlBuilder.append(" order by t.org_id,t.role_sort,t.create_time  ");
        this.findPageBySql(TSysRole.class, sqlBuilder.toString(), page, arrayList.toArray());
    }

    //根据角色名称查询角色全部列表
    @Override
    public List<TSysRole> findRoleListByRoleName(String roleId, String roleName) {
        String sql = "  select t.id,t.org_id,tod.name as org_name,t.role_name,t.role_code,t.role_type,t.status,t.app_id,t.domain_id,t.description,t.create_time,t.role_sort " +
                " from t_sys_role t left join t_org_dept tod on t.org_id=tod.id " +
                " where 1=1 " ;
        StringBuilder sqlBuilder = new StringBuilder(sql);

        ArrayList<Object> arrayList = new ArrayList<Object>();
        //角色id
        if (!StringUtil.isNullOrEmpty(roleId)) {
            sqlBuilder.append(" AND t.id = ? ");
            arrayList.add(roleId);
        }
        //角色名称
        if (!StringUtil.isNullOrEmpty(roleName)) {
            sqlBuilder.append(" AND t.role_name = ? ");
            arrayList.add(roleName);
        }

        sqlBuilder.append(" order by t.role_sort,t.create_time  ");
        return this.findAllBySQL(TSysRole.class, sqlBuilder.toString(), null,false, arrayList.toArray());
    }

    //新增角色
    @Override
    public void addRole(TSysRole sysRole) {
        String sql = "insert into t_sys_role (id,org_id,role_name,role_code,role_type,status,app_id,domain_id,description,create_time,role_sort) values ";
        StringBuilder sqlBuilder = new StringBuilder(sql);
        sqlBuilder.append("(#{id,jdbcType=VARCHAR},").append("#{org_id,jdbcType=VARCHAR},")
                .append("#{role_name,jdbcType=VARCHAR},").append("#{role_code,jdbcType=VARCHAR},")
                .append("#{role_type,jdbcType=VARCHAR},").append("#{status,jdbcType=VARCHAR},")
                .append("#{app_id,jdbcType=VARCHAR},").append("#{domain_id,jdbcType=VARCHAR},")
                .append("#{description,jdbcType=VARCHAR},").append("#{create_time,jdbcType=DATE},")
                .append("#{role_sort,jdbcType=INTEGER})");
        Map<String, Object> params = new HashMap<String, Object>();
        params.put("id", sysRole.getId());
        params.put("org_id", sysRole.getOrgId());
        params.put("role_name", sysRole.getRoleName());
        params.put("role_code", sysRole.getRoleCode());
        params.put("role_type", sysRole.getRoleType());
        params.put("status", sysRole.getStatus());
        params.put("app_id", sysRole.getAppId());
        params.put("domain_id", sysRole.getDomainId());
        params.put("description", sysRole.getDescription());
        params.put("create_time", new Timestamp(new Date().getTime()));
        params.put("role_sort", sysRole.getRoleSort());
        this.executeSql(sqlBuilder.toString(), params);
    }

    //更新角色
    @Override
    public void updateRole(TSysRole sysRole) {
        String sql = "update t_sys_role set ";
        StringBuilder sqlBuilder = new StringBuilder(sql);
        sqlBuilder.append("org_id=#{org_id,jdbcType=VARCHAR},")
                .append("role_name=#{role_name,jdbcType=VARCHAR},")
                .append("role_code=#{role_code,jdbcType=VARCHAR},")
                .append("role_type=#{role_type,jdbcType=VARCHAR},")
                .append("status=#{status,jdbcType=VARCHAR},")
                .append("app_id=#{app_id,jdbcType=VARCHAR},")
                .append("domain_id=#{domain_id,jdbcType=VARCHAR},")
                .append("description=#{description,jdbcType=VARCHAR},")
                .append("role_sort=#{role_sort,jdbcType=INTEGER}");
        sqlBuilder.append(" where 1=1 ");
        sqlBuilder.append(" and id = #{id,jdbcType=VARCHAR} ");
        Map<String, Object> params = new HashMap<String, Object>();
        params.put("id", sysRole.getId());
        params.put("org_id", sysRole.getOrgId());
        params.put("role_name", sysRole.getRoleName());
        params.put("role_code", sysRole.getRoleCode());
        params.put("role_type", sysRole.getRoleType());
        params.put("status", sysRole.getStatus());
        params.put("app_id", sysRole.getAppId());
        params.put("domain_id", sysRole.getDomainId());
        params.put("description", sysRole.getDescription());
        params.put("role_sort", sysRole.getRoleSort());
        this.executeSql(sqlBuilder.toString(), params);
    }

    //更新角色状态
    @Override
    public void updateRoleStatus(TSysRole sysRole) {
        String sql = "update t_sys_role set ";
        StringBuilder sqlBuilder = new StringBuilder(sql);
        sqlBuilder.append("status=#{status,jdbcType=VARCHAR}");
        sqlBuilder.append(" where 1=1 ");
        sqlBuilder.append(" and id = #{id,jdbcType=VARCHAR} ");
        Map<String, Object> params = new HashMap<String, Object>();
        params.put("id", sysRole.getId());
        params.put("status", sysRole.getStatus());
        this.executeSql(sqlBuilder.toString(), params);
    }

    //删除角色
    @Override
    public void deleteRole(String[] roleIds) {
        String sql = "delete from t_sys_role ";
        StringBuilder sqlBuilder = new StringBuilder(sql);
        sqlBuilder.append(" where 1=1 ");
        Map<String, Object> params = new HashMap<String, Object>();
        sqlBuilder.append(" and id in ('").append(String.join("','",roleIds)).append("') ");
        this.executeSql(sqlBuilder.toString(), params);
    }

    //查询已分配用户角色列表
    @Override
    public void selectAllocatedPageUser(Page<IscUser> page, IscUser iscUser, String roleId) {
        ArrayList<Object> arrayList = new ArrayList<>();
        StringBuilder sqlbuilder = new StringBuilder(" SELECT distinct TEBI.id as ID,TEBI.NAME as NAME,TEBI.erp_no as FAX,TOD.id as dept_id,TOD.NAME as ORG_ANISATION_NAME,TDER.org_id as BASEORG_ID," +
                "TEBI.mobile_phone as TEL,TEBI.cur_post as TITLE ");
        sqlbuilder.append(" FROM t_emp_base_info TEBI LEFT JOIN t_dept_emp_rela TDER ON TEBI.erp_no = TDER.erp_no LEFT JOIN t_org_dept TOD ON TOD.id = TDER.dept_id LEFT JOIN t_sys_user_role ur on TEBI.erp_no = ur.erp_no ");
        sqlbuilder.append(" WHERE 1=1 AND TEBI.del_sign = 0 AND TEBI.is_valid = 0 ");
        //roleId
        if (!StringUtil.isNullOrEmpty(roleId)) {
            sqlbuilder.append(" AND ur.role_id = ? ");
            arrayList.add(roleId);
        }
        if (null != iscUser) {
            //erp员工号
            if (!StringUtil.isNullOrEmpty(iscUser.getFax())) {
                sqlbuilder.append(" AND TEBI.erp_no LIKE concat('%',?,'%') ESCAPE '/'");
                String replaceStr = iscUser.getFax().replaceAll("%", "/%").replaceAll("_", "/_");
                arrayList.add(replaceStr);
            }
            //人员名称
            if (!StringUtil.isNullOrEmpty(iscUser.getName())) {
                sqlbuilder.append(" AND TEBI.name LIKE concat('%',?,'%') ESCAPE '/'");
                String replaceStr = iscUser.getName().replaceAll("%", "/%").replaceAll("_", "/_");
                arrayList.add(replaceStr);
            }
            if (!StringUtil.isNullOrEmpty(iscUser.getBaseorgId())) {
                sqlbuilder.append(" AND TOD.path LIKE concat('%',?,'%') ");
                arrayList.add(iscUser.getBaseorgId());
            }
        }
        sqlbuilder.append(" ORDER BY TEBI.sort ASC");
        this.findPageBySql(IscUser.class, sqlbuilder.toString(), page, arrayList.toArray());
    }

    //查询未分配用户角色列表
    @Override
    public void selectUnAllocatedPageUser(Page<IscUser> page, IscUser iscUser, String roleId) {
        ArrayList<Object> arrayList = new ArrayList<>();
        StringBuilder sqlbuilder = new StringBuilder(" SELECT distinct TEBI.id as ID,TEBI.NAME as NAME,TEBI.erp_no as FAX,TOD.id as dept_id,TOD.NAME as ORG_ANISATION_NAME,TDER.org_id as BASEORG_ID,TEBI.mobile_phone as TEL,TEBI.cur_post as TITLE ");
        sqlbuilder.append(" FROM t_emp_base_info TEBI LEFT JOIN t_dept_emp_rela TDER ON TEBI.erp_no = TDER.erp_no LEFT JOIN t_org_dept TOD ON TOD.id = TDER.dept_id LEFT JOIN t_sys_user_role ur on TEBI.erp_no = ur.erp_no ");
        sqlbuilder.append(" WHERE 1=1 AND TEBI.del_sign = 0 AND TEBI.is_valid = 0 ");
        //roleId
        if (!StringUtil.isNullOrEmpty(roleId)) {
            sqlbuilder.append(" and TEBI.erp_no not in ( select TEBI.erp_no from t_emp_base_info TEBI inner join t_sys_user_role ur on TEBI.erp_no = ur.erp_no and ur.role_id = ? ) ");
            arrayList.add(roleId);
        }
        if (null != iscUser) {
            //erp员工号
            if (!StringUtil.isNullOrEmpty(iscUser.getFax())) {
                sqlbuilder.append(" AND TEBI.erp_no LIKE concat('%',?,'%') ESCAPE '/'");
                String replaceStr = iscUser.getFax().replaceAll("%", "/%").replaceAll("_", "/_");
                arrayList.add(replaceStr);
            }
            //人员名称
            if (!StringUtil.isNullOrEmpty(iscUser.getName())) {
                sqlbuilder.append(" AND TEBI.name LIKE concat('%',?,'%') ESCAPE '/'");
                String replaceStr = iscUser.getName().replaceAll("%", "/%").replaceAll("_", "/_");
                arrayList.add(replaceStr);
            }
            if (!StringUtil.isNullOrEmpty(iscUser.getBaseorgId())) {
                sqlbuilder.append(" AND TOD.path LIKE concat('%',?,'%') ");
                arrayList.add(iscUser.getBaseorgId());
            }
        }
        sqlbuilder.append(" ORDER BY TEBI.sort ASC,TDER.org_id");
        this.findPageBySql(IscUser.class, sqlbuilder.toString(), page, arrayList.toArray());
    }

    //查询角色全部列表
    @Override
    public List<TSysRole> getRoleList(String roleName, String roleCode, String status, String orgId) {
        String sql = "  select distinct t.id,t.org_id,tod.name as org_name,t.role_name,t.role_code,t.role_type,t.status,t.app_id,t.domain_id,t.description,t.create_time,t.role_sort " +
                " from t_sys_role t left join t_org_dept tod on t.org_id=tod.id " +
                " where 1=1 " ;
        StringBuilder sqlBuilder = new StringBuilder(sql);

        ArrayList<Object> arrayList = new ArrayList<Object>();
        //当前登录用户的orgId
        if (!StringUtil.isNullOrEmpty(orgId)) {
            sqlBuilder.append(" and (t.org_id = -1 or t.org_id in ( select tod.id from t_org_dept tod where tod.path like concat('%',?,'%'))) ");
            arrayList.add(orgId);
        }
        //角色名称
        if (!StringUtil.isNullOrEmpty(roleName)) {
            sqlBuilder.append(" AND t.role_name like concat('%',?,'%') ");
            arrayList.add(roleName);
        }
        //角色编码
        if (!StringUtil.isNullOrEmpty(roleCode)) {
            sqlBuilder.append(" AND t.role_code like concat('%',?,'%') ");
            arrayList.add(roleCode);
        }
        //状态
        if (!StringUtil.isNullOrEmpty(status)) {
            sqlBuilder.append(" AND t.status = ? ");
            arrayList.add(status);
        }

        sqlBuilder.append(" order by t.org_id,t.role_sort,t.create_time  ");
        return this.findAllBySQL(TSysRole.class, sqlBuilder.toString(), null,false, arrayList.toArray());
    }

}
